﻿using System;
using System.Collections.Generic;
using System.Text;

/*
    Class to help with searching out extended properties 
	Sourced from the following page:
	http://www.nuronconsulting.com/TemplateScripts_Extended_PropertiesGetExtendedProperties_sql.aspx

see also documentation
http://technet.microsoft.com/en-us/library/ms190243(v=sql.105).aspx

*/
namespace SqlServerDriver
{
    class ExtendedPropertiesTemplates
    {
		// set some rules for other classes that use the data from these tables
		// see the scripts below
		public const String COLUMN_PROPERTY_NAME = "Property_Name";
		public const String COLUMN_PROPERTY_VALUE = "Property_Value";
		public const int	COLUMN_INDEX_SCHEMA_TABLE = 0;			// may need to name this column differently, but it is always the 1st column
		public const String COLUMN_TABLE_COLUMN = "Column_Name";	// column properties only

		// see PROPSUMMARY_TEMPLATE
		public static String GetExtendedPropertiesTemplate(DbView.Command command)
		{
			String itemType = command[DbView.Command.TableParameterName(0)]; 
			// ExtendedProperties command configures this.
			String propertyName = command["PROP"]; 
			return GetExtendedPropertiesTemplate(itemType, propertyName);
		}
		
		public static String GetExtendedPropertiesTemplate(String itemType, String propertyName)
		{
			String template = (itemType=="DATABASE") ? DATABASE_TEMPLATE :
					(itemType=="USER_TABLE") ? TABLE_TEMPLATE :
					(itemType=="COLUMN") ? COLUMN_TEMPLATE :
					(itemType=="SCHEMA") ? SCHEMA_TEMPLATE :
					(itemType=="PARAMETER") ? PARAMETER_TEMPLATE :
					(itemType=="INDEX") ? INDEX_TEMPLATE :
					(itemType=="FOREIGN_KEY_CONSTRAINT") ? FK_TEMPLATE :
					(itemType=="DEFAULT_CONSTRAINT") ? DEFAULT_TEMPLATE :
					(itemType=="DDL_TRIGGER") ? DDL_TRIGGER_TEMPLATE :
					(itemType=="CHECK_CONSTRAINT") ? CHECK_TEMPLATE :
					(itemType=="PRIMARY_KEY_CONSTRAINT") ? PK_TEMPLATE :
					(itemType=="SQL_SCALAR_FUNCTION") ? FUNCTION_TEMPLATE :
					(itemType=="SQL_STORED_PROCEDURE") ? PROC_TEMPLATE :
					(itemType=="SQL_TABLE_VALUED_FUNCTION") ? FUNCTION_TEMPLATE :
					(itemType=="SQL_TRIGGER") ? TRIGGER_TEMPLATE :
					(itemType=="VIEW") ? VIEW_TEMPLATE :
					(itemType=="XML_SCHEMA_COLLECTION") ? UNSUPPORTED_TEMPLATE :
					(itemType=="DATASPACE") ? DATASPACE_TEMPLATE :
					(itemType=="DATABASE_FILE") ? DATAFILE_TEMPLATE :
					UNSUPPORTED_TEMPLATE;
			return String.Format(template, propertyName);
		}

const String TABLE_TEMPLATE = @"
SELECT  SCH.name + '.' + TBL.name AS TABLE_NAME,
        SEP.name AS Property_Name,
        SEP.value AS Property_Value
FROM    sys.tables TBL
INNER JOIN sys.schemas SCH ON TBL.SCHEMA_ID = SCH.SCHEMA_ID
INNER JOIN sys.extended_properties SEP ON TBL.OBJECT_ID = SEP.major_id
WHERE   SEP.CLASS = 1
        AND SEP.minor_id = 0
        AND (SEP.value <> '1'
             AND SEP.value <> 1)
		and SEP.name = '{0}'
 [[ and ((UPPER(SCH.name) like '%%')
       or (UPPER(TBL.name) like '%%')
	   or (UPPER(SCH.name + '.' + TBL.name) like '%%')
	   or (UPPER(SEP.name) like '%%')
	   or (UPPER(convert(varchar(1024), SEP.value)) like '%%'))
 ]]order by SCH.name, TBL.name, SEP.name";

const String COLUMN_TEMPLATE = @"
SELECT  SCH.name + '.' + TBL.name AS TABLE_NAME,
        COL.name AS Column_Name,
        SEP.name AS Property_Name,
        SEP.value AS Property_Value
FROM    sys.extended_properties SEP
INNER JOIN sys.columns COL ON SEP.major_id = COL.OBJECT_ID
                              AND SEP.minor_id = COL.column_id
INNER JOIN sys.tables TBL ON SEP.major_id = TBL.OBJECT_ID
INNER JOIN sys.schemas SCH ON TBL.SCHEMA_ID = SCH.SCHEMA_ID
WHERE SEP.CLASS = 1
  AND (SEP.value <> '1' AND SEP.value <> 1)
  and SEP.name = '{0}'
 [[ and ((UPPER(SCH.name) like '%%')
       or (UPPER(TBL.name) like '%%')
	   or (UPPER(SCH.name + '.' + TBL.name) like '%%')
	   or (UPPER(COL.name) like '%%')
	   or (UPPER(SEP.name) like '%%')
	   or (UPPER(convert(varchar(1024), SEP.value)) like '%%'))
 ]]
order by SCH.name, TBL.name, SEP.minor_id, SEP.name
";

const String FK_TEMPLATE = @"
SELECT  SCH.name + '.' + TBL.name AS TABLE_NAME,
        SFK.name AS FOREIGN_KEY_NAME,
        SEP.name AS Property_Name,
        SEP.value AS Property_Value
FROM    sys.extended_properties SEP
INNER JOIN sys.foreign_keys SFK ON SEP.major_id = SFK.OBJECT_ID
INNER JOIN sys.tables TBL ON SFK.parent_object_id = TBL.OBJECT_ID
INNER JOIN sys.schemas SCH ON TBL.SCHEMA_ID = SCH.SCHEMA_ID
 where SEP.name = '{0}'
 [[ and ((UPPER(SCH.name) like '%%')
       or (UPPER(TBL.name) like '%%')
	   or (UPPER(SCH.name + '.' + TBL.name) like '%%')
	   or (UPPER(SEP.name) like '%%')
	   or (UPPER(convert(varchar(1024), SEP.value)) like '%%'))
 ]]
order by SCH.name + '.' + TBL.name, SFK.name, SEP.name
";

		const string DATABASE_TEMPLATE = @"
SELECT DB_NAME() as Database_Name,
       SEP.name AS Property_Name,
       SEP.value AS Property_Value
FROM   sys.extended_properties SEP
WHERE  class_desc = N'DATABASE'
  and  SEP.name = '{0}'";

		const string SCHEMA_TEMPLATE = @"
SELECT  SCH.name AS SCHEMA_NAME,
        SEP.name AS Property_Name,
        SEP.value AS Property_Value
FROM    sys.extended_properties SEP
INNER JOIN sys.schemas SCH ON SEP.major_id = SCH.SCHEMA_ID
WHERE SEP.class_desc = N'SCHEMA'
  and SEP.name = '{0}'
[[ and (UPPER(SCH.name) like '%%') ]]";
 
		const string PARAMETER_TEMPLATE = @"
SELECT  SCH.name +'.'+OBJ.name AS Name,
		case when OBJ.type in  ('FN', 'IF', 'TF') then 'Function'
			 when OBJ.type in  ('P') then 'Procedure'
			 else '' end as Object_type, 
        PRM.name AS ParameterName,
        SEP.name AS Property_Name,
        SEP.value AS Property_Value
FROM    sys.extended_properties SEP
INNER JOIN sys.objects OBJ ON SEP.major_id = OBJ.OBJECT_ID
INNER JOIN sys.schemas SCH ON OBJ.SCHEMA_ID = SCH.SCHEMA_ID
INNER JOIN sys.parameters PRM ON SEP.major_id = PRM.OBJECT_ID
                                 AND SEP.minor_id = PRM.parameter_id
WHERE   SEP.class_desc = N'PARAMETER'
  and SEP.name = '{0}'
 [[ and (UPPER(SCH.name) like '%%'
		  or UPPER(SCH.name +'.'+OBJ.name) like '%%'
          or UPPER(OBJ.name) like '%%')
  ]]
order by SCH.name, case when OBJ.type in  ('FN', 'IF', 'TF') then 'F'
		 when OBJ.type in  ('P') then 'P'
		 else 'Z' end,  OBJ.name, SEP.minor_id, PRM.parameter_id";

		const string INDEX_TEMPLATE = @"
 SELECT  SCH.name + '.' + TBL.name AS TABLE_NAME,
        SIX.name AS INDEX_NAME,
        SEP.name AS Property_Name,
        SEP.value AS Property_Value
 FROM  sys.indexes SIX
INNER JOIN sys.extended_properties SEP ON SIX.OBJECT_ID = SEP.major_id
                                          AND SIX.index_id = SEP.minor_id
INNER JOIN sys.tables TBL
INNER JOIN sys.schemas SCH ON TBL.SCHEMA_ID = SCH.SCHEMA_ID ON SIX.OBJECT_ID = TBL.OBJECT_ID
WHERE   SEP.class_desc = N'INDEX'
        AND SIX.is_primary_key = 0
		and SEP.name = '{0}'
		[[ and (UPPER(SCH.name) like '%%'
				 or UPPER(TBL.name) like '%%'
				  or UPPER(SCH.name + '.' + TBL.name) like '%%'
				  or UPPER(SEP.name) like '%%'
				  or UPPER(SIX.name) like '%%') ]]
order by SCH.name, TBL.name, SIX.name, SEP.name";

		const string DEFAULT_TEMPLATE = @"
SELECT  SCH.name + '.' + TBL.name AS TABLE_NAME,
        SDC.name AS Default_Name,
		SEP.name AS Property_Name,
        SEP.value AS Property_Value
FROM    sys.default_constraints SDC
INNER JOIN sys.schemas SCH
INNER JOIN sys.tables TBL ON SCH.SCHEMA_ID = TBL.SCHEMA_ID ON SDC.parent_object_id = TBL.OBJECT_ID
INNER JOIN sys.extended_properties SEP ON SDC.OBJECT_ID = SEP.major_id
 where SEP.name = '{0}'
 [[ and ((UPPER(SCH.name) like '%%')
       or (UPPER(TBL.name) like '%%')
	   or (UPPER(SCH.name + '.' + TBL.name) like '%%')
	   or (UPPER(SEP.name) like '%%')
	   or (UPPER(convert(varchar(1024), SEP.value)) like '%%'))
 ]]
 order by SCH.name + '.' + TBL.name, SDC.name, SEP.name";

		const string DDL_TRIGGER_TEMPLATE = @"
 SELECT  TRG.name AS DDL_Trigger_Name,
        SEP.name AS Property_Name,
        SEP.value AS Property_Value
FROM    sys.extended_properties SEP
INNER JOIN sys.triggers TRG ON SEP.major_id = TRG.OBJECT_ID
WHERE   TRG.parent_class_desc = N'DATABASE'
  and SEP.name = '{0}'
";

		const string CHECK_TEMPLATE = @"
SELECT  SCH.name + '.' + TBL.name AS TABLE_NAME,
        CHK.name AS CheckConstraintName,
        SEP.name AS Property_Name,
        SEP.value AS Property_Value
FROM    sys.extended_properties SEP
INNER JOIN sys.check_constraints CHK ON SEP.major_id = CHK.OBJECT_ID
INNER JOIN sys.schemas SCH
INNER JOIN sys.tables TBL ON SCH.SCHEMA_ID = TBL.SCHEMA_ID ON CHK.parent_object_id = TBL.OBJECT_ID
where SEP.name = '{0}'
 [[ and ((UPPER(SCH.name) like '%%')
       or (UPPER(TBL.name) like '%%')
	   or (UPPER(SCH.name + '.' + TBL.name) like '%%')
	   or (UPPER(SEP.name) like '%%')
	   or (UPPER(convert(varchar(1024), SEP.value)) like '%%'))
 ]]
 order by SCH.name + '.' + TBL.name, CHK.name, SEP.name
";

		const string VIEW_TEMPLATE = @"
SELECT  SCH.name + '.' + SVW.name AS VIEW_NAME,
        SEP.name AS Property_Name,
        SEP.value AS Property_Value
FROM    sys.extended_properties SEP
INNER JOIN sys.views SVW ON SEP.major_id = SVW.OBJECT_ID
INNER JOIN sys.schemas SCH ON SVW.SCHEMA_ID = SCH.SCHEMA_ID
WHERE   SEP.minor_id = 0
  and SEP.name = '{0}'
[[ and ((UPPER(SCH.name) like '%%')
       or (UPPER(SVW.name) like '%%')
	   or (UPPER(SCH.name + '.' + SVW.name) like '%%')
	   or (UPPER(SEP.name) like '%%')
	   or (UPPER(convert(varchar(1024), SEP.value)) like '%%'))
 ]]
order by SCH.name + '.' + SVW.name, SEP.name";

		const string FUNCTION_TEMPLATE = @"
SELECT   SCH.name + '.' + SOB.name AS FUNCTION_NAME,
        SEP.name AS Property_Name,
        SEP.value AS Property_Value
FROM    sys.extended_properties SEP
INNER JOIN sys.objects SOB ON SEP.major_id = SOB.OBJECT_ID
INNER JOIN sys.schemas SCH ON SOB.SCHEMA_ID = SCH.SCHEMA_ID
WHERE   SOB.type_desc LIKE N'%FUNCTION%'
        AND SEP.minor_id = 0
		and SEP.name = '{0}'
[[ and ((UPPER(SCH.name) like '%%')
       or (UPPER(SOB.name) like '%%')
	   or (UPPER(SCH.name + '.' + SOB.name) like '%%')
	   or (UPPER(SEP.name) like '%%')
	   or (UPPER(convert(varchar(1024), SEP.value)) like '%%'))
 ]]
order by SCH.name + '.' + SOB.name, SEP.name";

		const string PK_TEMPLATE = @"
SELECT  SCH.name + '.' + TBL.name AS TABLE_NAME,
        SKC.name AS KeyName,
        SEP.name AS Property_Name,
        SEP.value AS Property_Value
FROM    sys.tables TBL
INNER JOIN sys.schemas SCH ON TBL.SCHEMA_ID = SCH.SCHEMA_ID
INNER JOIN sys.extended_properties SEP
INNER JOIN sys.key_constraints SKC ON SEP.major_id = SKC.OBJECT_ID ON TBL.OBJECT_ID = SKC.parent_object_id
WHERE   SKC.type_desc = N'PRIMARY_KEY_CONSTRAINT'
  and SEP.name = '{0}'
[[ and ((UPPER(SCH.name) like '%%')
	   or (UPPER(SCH.name + '.' + TBL.name) like '%%')
       or (UPPER(SKC.name) like '%%')
	   or (UPPER(SEP.name) like '%%')
	   or (UPPER(convert(varchar(1024), SEP.value)) like '%%'))
 ]]
order by SCH.name + '.' + SKC.name, SEP.name";

		const string PROC_TEMPLATE = @"
SELECT SCH.name + '.' + PRC.name AS PROC_NAME,
        SEP.name AS Property_Name,
        SEP.value AS Property_Value
FROM    sys.extended_properties SEP
INNER JOIN sys.procedures PRC ON SEP.major_id = PRC.OBJECT_ID
INNER JOIN sys.schemas SCH ON PRC.SCHEMA_ID = SCH.SCHEMA_ID
WHERE   SEP.minor_id = 0
  and SEP.name = '{0}'
[[ and ((UPPER(SCH.name) like '%%')
       or (UPPER(PRC.name) like '%%')
	   or (UPPER(SCH.name + '.' + PRC.name) like '%%')
	   or (UPPER(SEP.name) like '%%')
	   or (UPPER(convert(varchar(1024), SEP.value)) like '%%'))
 ]]
order by SCH.name + '.' + PRC.name, SEP.name
";

		const string TRIGGER_TEMPLATE = @"
SELECT  SCH.name + '.' + TBL.name AS TABLE_NAME,
        TRG.name AS TRIGGER_NAME,
        SEP.name AS Property_Name,
        SEP.value AS Property_Value,
        TBL.OBJECT_ID AS ObjectID
FROM    sys.tables TBL
INNER JOIN sys.triggers TRG ON TBL.OBJECT_ID = TRG.parent_id
INNER JOIN sys.extended_properties SEP ON TRG.OBJECT_ID = SEP.major_id
INNER JOIN sys.schemas SCH ON TBL.SCHEMA_ID = SCH.SCHEMA_ID
 where SEP.name = '{0}'
   and SEP.name = '{0}'
[[ and ((UPPER(SCH.name) like '%%')
       or (UPPER(TBL.name) like '%%')
	   or (UPPER(SCH.name + '.' + TBL.name) like '%%')
       or (UPPER(TRG.name) like '%%')
	   or (UPPER(SEP.name) like '%%')
	   or (UPPER(convert(varchar(1024), SEP.value)) like '%%'))
 ]]
order by SCH.name + '.' + TBL.name, TRG.name, SEP.name
";

		const string DATASPACE_TEMPLATE = @"
SELECT  DSP.name AS DataSpace,
        SEP.name AS Property_Name,
        SEP.value AS Property_Value,
        DSP.data_space_id AS DataSpaceID
FROM    sys.extended_properties SEP
INNER JOIN sys.data_spaces DSP ON SEP.major_id = DSP.data_space_id
WHERE   DSP.type_desc = 'ROWS_FILEGROUP'
  and SEP.name = '{0}'
";

		const string DATAFILE_TEMPLATE = @"
SELECT  DSP.name AS DataSpaceName,
        DBF.name AS LogicalFileName,
        SEP.name AS Property_Name,
        SEP.value AS Property_Value
FROM    sys.extended_properties SEP
INNER JOIN sys.database_files DBF ON SEP.major_id = DBF.FILE_ID
INNER JOIN sys.data_spaces DSP ON DBF.data_space_id = DSP.data_space_id
WHERE   SEP.class_desc = N'DATABASE_FILE'
  and SEP.name = '{0}'
";

		const string UNSUPPORTED_TEMPLATE = @"
select 'Sorry, properties of this type are inaccessible in this version of DbViewSharp' as Message_of_apology
";
    }
}
